package www.lagou.dao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import www.lagou.entity.Dept;
import www.lagou.entity.Employee;
import www.lagou.utils.DruidUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 员工操作类
 */
public class EmployeeDao {
    private QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());

    /**
     * 查询所有员工信息（不包含没有部门的员工）
     * @return 查询结果
     * @throws SQLException
     */
    public List<Employee> findEmployees() throws SQLException {
        String sql = "select * from employee where did is not null ";
        List<Employee> employees = queryRunner.query(sql, new BeanListHandler<Employee>(Employee.class));
        if (employees != null || employees.size() != 0)
            return employees;
        else
            return null;
    }


    /**
     * 查询每个员工的姓名，薪资和所属部门名称
     * @return 返回查询结果
     * @throws SQLException
     */
    public List<List<String>> findEmployeesInfo() throws SQLException {
        List<List<String>> employeesInfo = new ArrayList<>();
        String sql = "select * from employee";
        List<Employee> employees = queryRunner.query(sql, new BeanListHandler<Employee>(Employee.class));
        if (employees != null || employees.size() != 0){
            for (Employee e:employees) {
                List<String> info = new ArrayList<>();
                if (e.getDid() != null){
                    Dept dept = findDeptNameById(e.getDid());
                    e.setDept(dept);
                    info.add("姓名：" + e.getName() + " ");
                    info.add("薪资：" + e.getSalary() + " ");
                    info.add("所属部门：" + e.getDept().getDeptname());
                }else{
                    e.setDept(null);
                    info.add("姓名：" + e.getName() + " ");
                    info.add("薪资：" + e.getSalary() + " ");
                    info.add("所属部门：" + null);
                }
                employeesInfo.add(info);
            }
            return employeesInfo;
        }else
            return null;
    }
    /**
     * 根据部门id返回部门名
     * @param id 部门id
     * @return 部门
     * @throws SQLException
     */
    public Dept findDeptNameById(int id) throws SQLException {
        String sql = "select deptname from dept where id = ?";
        Dept dept = queryRunner.query(sql, new BeanHandler<Dept>(Dept.class), id);
        if (dept != null)
            return dept;
        else
            return null;
    }
}
